# coding: utf-8

# NOTE by zhouhl 2018-02-05
# 将pack表中的area json字段拆分到 user_to_area 表中，提高sql查询效率

import os
import json

import set_env_path     # 请勿删除
from sqlalchemy import create_engine
from sqlalchemy.sql import text


engine = create_engine(
    os.getenv('DATABASE_URL')
    or 'mysql+pymysql://root:@127.0.0.1/wph_www_prod?charset=utf8'
)
conn = engine.connect()


def main():
    query_pack_sql = text("""
        select
            uid, area_id
        from
            pack
        where
            deleted_at=0
        order by
            uid;
    """)

    insert_sql = text("""
        insert into user_to_area (
            user_id, area_id
        ) values (
            :user_id, :area_id
        );
    """)

    pack_records = conn.execute(query_pack_sql).fetchall()
    for i in pack_records:
        user_id = i.uid
        citys = json.loads(i.area_id)
        citys = set(citys)
        for city_id in citys:
            print(user_id, city_id)
            conn.execute(
                insert_sql,
                user_id=user_id,
                area_id=city_id,
            )


if __name__ == '__main__':
    main()
